<?php
// Lots of these functions can be condensed.
/**********************************************************************************
 
 	Provides access to a MySQL database to create market tools for WoW.
	
	Copyright (C) 2010 Benjamin Thomas
 
    This program is free software: you can redistribute it and/or modify
    it under the terms of the GNU General Public License as published by
    the Free Software Foundation, either version 3 of the License, or
    (at your option) any later version.

    This program is distributed in the hope that it will be useful,
    but WITHOUT ANY WARRANTY; without even the implied warranty of
    MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
    GNU General Public License for more details.

    You should have received a copy of the GNU General Public License
    along with this program.  If not, see <http://www.gnu.org/licenses/>.
 
 	Author: Benjamin Thomas
	Email: thomas.ben@gmail.com
 	Created: 05.21.10
 	Revised: 07.15.10
 
	Inputs: To be completed
 	Calls: To be completed
	
 
 ***********************************************************************************
 */
 
 require_once(dirname(__FILE__) . "/../config.php");
 require_once(dirname(__FILE__) . "/netFunctions.php");

class MarketDB {
	//user name for the 
	protected $username;
	protected $password;
	protected $database;
	protected $connection;
	protected $serverID = 0;
	public $items = array();
	public $professions = array();
	protected $chars = array();
	
	/*
		This function should be disabled when not testing.
	*/
	public function publicQuery($sql) {
		return $this->query($sql);
	}
	//*/
	
	public function __construct() {
		//Retrieve files from config.php
		$this->username = Vars::$dbuser;
		$this->password = Vars::$dbpwd;
		$this->database = Vars::$database;
		//Connect to database
		$this->connection = mysql_connect('localhost', $this->username, $this->password);
		if (!$this->connection) {
			die('Could not connect: ' . mysql_error());
		}
		@mysql_select_db($this->database, $this->connection) or die( "Unable to select database" . mysql_error());
		
		//check session for server
		if (isset($_GET['server'])) {
			$this->serverID = $_GET['server'];
			$_COOKIE['server'] = $_GET['server'];
		} elseif (isset($_COOKIE['server'])) {
			$this->serverID = $_COOKIE['server'];
		} else {
			$_COOKIE['server'] = $this->serverID;
		}
	}
	
	public function toArray() {
		$tmp = array();
		$tmp['username'] = $this->username;
		$tmp['database'] = $this->database;
		$tmp['serverID'] = $this->serverID;
		$tmp['items'] = $this->items;
		$tmp['chars'] = $this->chars;
		$tmp['cookie'] = $_COOKIE['server'];
		return $tmp;
	}
	
	public function __decontruct() {
		mysql_close($this->connection);
	}
	
	/*
		setServer ( str $name )
		Sets the server used in this database.
		Parameters:
			$name	Name of the server Format: Name - Alliance/Horde
		Returns 
			int 	The database id of the server given.
	*/
	public function setServer($name) {
		if (!$this->serverID = $this->selectdata("realms", "id", "name = " . $this->formatString($name))) {		
			$this->insert("realms",array("name" => $name));
			$this->serverID = $this->setServer($name);
		}
		return $this->serverID;
	}
	
	/*
		getServers ( )
		Returns all servers from the database
		Parameters:
			None
		Returns:
			array:	str name	Name of the server
					int	id		Database's id of the server
	*/	
	public function getServers() {
		return $this->select("realms", "name,id", "1", "ORDER BY name ASC", false);
	}
	
	/*
		getServerId( ) 
		Returns the currently selected serverID
		Parameters:
			None
		Returns:
			int	Database's id of the server
	*/	
	public function getServerId() {
		return $this->serverID;
	}
	
	/*
		getServerName( )
		Returns the name of the currently selected server
		Parameters:
			None
		Returns:
			string	Name of the server
	*/	
	public function getServerName() {
		return $this->selectdata("realms","name","id = " . $this->serverID, "", false);
	}
	
	/*
		getChar( string $name, int $server = null) 
		Return charater's id. If server is provided and none exists, it will be created
		Parameters:
			name	Name of the character
			server	Database's id of the server
		Returns:
			int		Database's id for the character
			false	If character not found and unable to create
	*/
	public function getChar($name, $server = null) {
		if (!$result = $this->selectdata("chars", "charID", "name = '$name' AND server = {$this->serverID}","", false)) {
			if ($server != null) {
				$this->insert("chars", array( "name" => "$name", 'server' => $server ), true);
				return $this->getChar($name);
			} else {
				error('Unable to find character in the database and server was not provided.');
				return false;
			}
		}
		return $result;
	}
	
	/*
		getChars( )
		Return all of the characters in the database
		Parameters:
			None
		Returns:
			array:	name	Name of the character
					wealth	Gold currently held by the character (does not include gbanks)
					charid	Database's ID for the character
	*/
	public function getChars() {
		return $this->select("chars", array("name", "wealth", "charid"), "server = {$this->serverID}", "ORDER BY name", false);
	}
	
	/*
		updateChar( int $id, int $wealth )
		Updates the basic information of a character in the database.
		Parameters:
			id		Database's ID for the character.
			wealth	Gold currently held by the character
	*/
	public function updateChar($id, $wealth) {
		$this->update("chars", "wealth", $wealth, "charid = $id", true);
	}
	
	/*
		checkCompletedAuction( int $item, int $time, int $char )
		This function will check to see if the auction previously exists using unique data of the item, the time recieved,
		and unique character id.
		Parameters:
			item	Database's id for the item
			time	Unix timestamp when the auction was sold
			char	Database's id for the character that posted
		Returns:
			boolean	Success/Failure
	*/
	public function checkCompletedAuction($item, $time, $char) {
		//$sql = "SELECT auctionID FROM completedAuctions WHERE itemId = $item AND time = $time AND charID = $char AND server = " . $this->serverID;
		if ($this->select("completedAuctions", "auctionID", "itemId = $item AND time = $time AND charID = $char AND server = {$this->serverID}","", false)) {
			return true;
		} else {
			return false;
		}	
	}
	
	/*
		createCompletedAuction ( int $stack, int $money, int $deposit, int $fee, int $buyout, int $bid, str $buyer, int $time
				int $faction, int $char, int $item )
		This function will add new completed auction data to the database.
		Parameters
			stack		Stack size of the auction (1-20)
			money		Money collected from sale after auction house cut
			deposit		Deposit paid to create auction
			fee			Fee paid to auction house
			buyout		Buyout price of posted auction
			bid			Bid price of posted auction
			buyer		Character that purchases your item
			time		Unix timestamp when auction was sold
		Returns:
			None
	*/
	public function createCompletedAuction($stack, $money, $deposit, $fee, $buyout, $bid, $buyer, $time, $faction, $char, $item) {
		$val = array();
		$val["stackcount"] = $stack; $val['money'] = $money; $val['deposit'] = $deposit; $val['fee'] = $fee; $val['buyout'] = $buyout;
		$val['bid'] = $bid; $val['buyer'] = $buyer; $val['time'] = $time; $val['faction'] = $faction; $val['server'] = $this->serverID;
		$val['charid'] = $char; $val['itemid'] = $item;
		$this->insert("completedAuctions", $val, true);
	}
	
	/*
		checkBuyout ( int $item, int $time, int $char, array $val = 0 )
		Check to see if the entry exists in completedbuyouts and create if $val is passed
		Parameters:
			item	Database's id of the time
			time	Unix timestamp of the 
			char	Database's id of the char
			val:	[UNKNOWN]
		Returns:
			id 		Database's id of the completed auction
	*/
	public function checkBuyout($item, $time, $char, $val = 0) {
		if (!$check = $this->select("completedbuyouts", "buyoutid", "itemid = $item AND charid = $char and time = $time","", false)) {
			if ($val != 0) {
				$val['time'] = $time;
				$val['itemid'] = $item;
				$val['charid'] = $char;
				$this->insert("completedbuyouts", $val, true);
				//Don't try to re-insert if it fails
				return $this->checkBuyout($item, $time, $char);
			} else { 
				return false;
			}
		}
		return $check;
	}

	/*
		checkItem ( int $id, int $suffix = "", str $name = "", str $color = "" )
		Checks to see if item exists in the database and creates if it does not and name/color are supplied.
		Parameters:
			id		Database's id of the item
			suffix	Unused
			name	Item's name
			color	Unused
		Returns:
			boolean
	*/
	public function checkItem($id, $suffix = "", $name = "", $color = "") {
		if ($this->selectrow("items", "itemid", "itemid = $id","",false)) {
			return true;
		} else {
			return false;
		}
	}
	
	/*
		getItem ( int/str	$item )
		Returns the id of the item provided
		Paramaters:
			str item	Name of the item
			int item	Database's id for the item
		Returns
			array	price		Vendor price
					quality		Quality of the item 0-6
					icon		Filename of the icon
					name		Name of the icon
					itemid		Database's id of the item
	*/
	public function getItem($mixed) {
		//ob_flush();
		if (!intval($mixed)) {
			$id = $this->selectdata("items", "itemid", "name = " . $this->formatString($mixed), "",  true);
			if (!$id) {
				$id = getItemId($mixed);
				if (!$id) { return false; }
			}
		} else {
			$id = $mixed;
		}
		if (!$data = $this->selectrow("items","price, quality, icon, name, itemid", "itemid = $id","", false)) {
			if (!$data = getItemData($id)) {
				return false;
			} else {
				$data['itemid'] = $id;
				$this->insert("items", $data, true);
			}
		}
		return $data;			
	}
		
	/*
		addItem ( int $id, int $name, int $price, int $quality, str $icon )
		Insert item into the database
		Parameters:
			id 			Database's id of the item
			name		Name of the item
			price		Vendor price of the item
			quality		Quality of the item 0-5
			icon		Filename of the icon
		Returns:
			None
	*/		
	public function addItem($id, $name, $price, $quality, $icon) {
		$val = array("name" => $name, "price" => $price, "quality" => $quality, 'icon' => $icon, 'itemid' => $id);
		$this->insert("items", $val, true);
	}
	
	/*
		TO BE DEPRECIATED
	*/
	public function getItemColor($id) {
		return $this->items[$id]['color'];
	}
	
	/*
		getItemName ( int $id )
		Get the name of the items from the item array, not database.
		Parameters:
			id	Database's id for the item
		Returns:
			str	Name of the item
	*/
	public function getItemName($id) {
		if (isset($this->items[$id])) {
			return $this->items[$id]['name'];
		}
	}
	
	/*
		loadItems ( int $id = '' )
		Get the item information from the item array, not the database
		Parameters:
			id	Database's id for the item
		Returns:
			None
	*/		
	public function loadItems($id = '') {
		$sql = "SELECT icon, name, quality, price, itemID FROM items";
		if ($id) {
			$sql .= " WHERE itemid = $id";
		}
		$result = $this->query($sql);
		for ($i = 0; $i < count($result); $i++) {
			$current = $result[$i];	
			$this->items[ $current['itemID'] ] = array('price' => $current['price'], 'icon' => $current['icon'], 'name' => $current['name']);
		}
	}
	
	/*
		loadProffesions ( ) 
		Get the professions and load them into the object
		Parameters:
			None
		returns:
			None
	*/
	public function loadProfessions() {
		$result = $this->select("profession", "name, icon, id");
		$this->prof = array();
		foreach($result as &$row) {
			$this->prof[$row['id']]['name'] = $row['name'];
			$this->prof[$row['id']]['icon'] = $row['icon'];
		}
	}
	
	/*
		getProfession ( int id )
		Returns information about the profession
		Parameters:
			id	Databases's id for the profession
		Returns:
			array	name	Name of the profession
					icon	Filename of the profession's icon
			false	On failure
	*/	
	public function getProfession($id) {
		if (array_key_exists($id, $this->prof)) {
			return $this->prof[$id];
		} else {
			return false;
		}
	}
	
	/*
		getSales(	int $days )
		Returns all sales for specified time in an array ('stackCount', 'money', 'buyer', 'time')
		Parameters:
			days	Days from today to retrieve
			rLimit	To be depreciated
			page	To be depreciated
		Returns:
			array	stackCount	Size of the stack
					money		Money returned
					buyer		Character that bought the item
					day			Day (M/D) item was purchase
					time		Unix timestamp the auction was bought
					itemID		Database's id of the item
	*/
	public function getSales($days, $id = null) {
		$limit = time() - ($days * 60*60*24);
		$where = "server = {$this->serverID}";
		if ($id) {
			$where .= " AND itemID = $id";
		}
		if ($days > 0) {
			$where .= " AND FROM_UNIXTIME(time, '%m/%d/%y') > FROM_UNIXTIME($limit, '%m/%d/%y')";
		}
		return $this->select("completedAuctions", "stackCount, money, buyer, FROM_UNIXTIME(time, '%c/%e') as day, time, itemID", $where, "ORDER BY time", false);
	}	
	
	/*
		getSalesSummary ( int $days, int $id ) 
		Get summary for a period
		Parameters:
			days	Days from today to include in period
			id		Database's id of the item to limit results to
		Returns:
			array	total	Total ammount of gold earned
					money	Total money earned
					min		Minimum sale price
					max		Maximum sale price
					day		Date in M/D format
					date	Date in M/DD/YY format
	*/
	public function getSalesSummary($days, $id = null) {
		$limit = time() - ($days * 60*60*24);
		$where = "server = {$this->serverID}";
		$col = "count(auctionid) as total, sum(money) as money, min(money/stackcount) as min, max(money/stackcount) as max,";
		$col .= " FROM_UNIXTIME(time, '%c/%e') as day, FROM_UNIXTIME(time,'%c/%d/%y') as date";
		if ($id) {
			$where .= " AND itemID = $id";
		}
		if ($days > 0) {
			$where .= " AND FROM_UNIXTIME(time, '%m/%d/%y') > FROM_UNIXTIME($limit, '%m/%d/%y')";
		}
		$extra = "GROUP BY day";
		return $this->select("completedAuctions", $col, $where, $extra, false);
	}
		
	/*
		getPurchases( int $days, int 4id = null )
		Get all purchases for a time period.
		Parameters:
			days	Days from today to include in period
			id		Database's id of the item
		Returns:
			array	int	stackCount	Size of the stack
					int price		Gold spent on purchase
					str seller		Character selling the gold
					int time		Unix timestamp when the item was bought
					int itemID		Database's id for the item
	*/	
	public function getPurchases($days, $id = null) {
		$limit = time() - ($days * 60*60*24);
		$where = "chars.server = {$this->serverID}";
		if ($id) {
			$where .= " AND itemid = $id";
		}
		if ($days > 0 ) { 
			$where .= " AND FROM_UNIXTIME(time, '%m/%d/%y') > FROM_UNIXTIME($limit, '%m/%d/%y')";
		}
		$table = "completedBuyouts as buy LEFT JOIN chars ON chars.charID = buy.charID";
		return $this->select($table, "stackCount, price, seller, time, itemID", $where, "ORDER BY time DESC", false);
	}
	
	/*
		getPurchasesSummary ( $days )
		Get the purchase summary for a time period.
		Parameters:
			days	Days from today to include in period.
			id		Database's id of an item
		Returns:
			array	int total	Total purchases completed
					int spent	Total gold spent
					str day		Day (m/d format)
	*/
	public function getPurchasesSummary($days, $id = null) {
		$limit = time() - ($days * 60*60*24);
		$where = "chars.server = {$this->serverID}";
		if ($id) {
			$where .= " AND buy.itemID = $id";
		}
		if ($days > 0) {
			$where .= " AND FROM_UNIXTIME(time, '%m/%d/%y') > FROM_UNIXTIME($limit, '%m/%d/%y')";
		}
		$table = "completedBuyouts as buy LEFT JOIN chars ON chars.charID = buy.charID";
		$extra = "GROUP BY day";
		return $this->select($table, "count(buyoutid) as total, sum(price) as spent, FROM_UNIXTIME(time, '%c/%e') as day", $where, $extra, false);
	}
		
	/*
		getSalesAndPurchases ( int $days, int $id = null )
		Returns data on the sales and purchases for a period from today
		Parameters:
			days	Days from today to include in the period
			id		If supplied, limits results to the id
		Returns:
			array	itemid			Database's id of the item
					stackCount		Size of the stack 
					money			Ammount earned/spent on the auction
					person			Seller/Buyer 
					time			Unix timestamp of the auction
					set				0 for a sale, 1 for a purchase
	*/	
	public function getSalesAndPurchases($days, $id = null) {
		$limit = time() - ($days * 60*60*24);
		$col = "itemid, stackCount, money, buyer AS person, time, 0 AS  'set'";
		$where = "server = {$this->serverID}";
		if ($id) {
			$where .= " AND itemid = $id";
		}
		if ($days > 0) {
			$where .= " AND FROM_UNIXTIME(time, '%m/%d/%y') > FROM_UNIXTIME($limit, '%m/%d/%y')";
		}
		$table = "completedauctions WHERE $where UNION SELECT itemid, stackCount, price AS money, seller AS person, time, 1 AS  'set'";
		$table .= " FROM completedbuyouts LEFT JOIN chars ON completedbuyouts.charid = chars.charid";
		return $this->selectcount($table, $col, $where, "ORDER BY TIME DESC", false);
	}
		
	// NOTE: This function needs to be changed to the new database schema
	public function getScanAverageByID($id, $days) {
		$time = createDate($days);
		$select = "avg(buyoutprice/stacksize) as avg";
		$where = "itemID = $id AND FROM_UNIXTIME(timestamp, '%m/%d/%y') > FROM_UNIXTIME($time, '%m/%d/%y')";
		$where .= " AND realm = {$this->serverID} AND buyoutprice > 0";
		return $this->selectrow("scans", $select, $where, "", false);		
	}
	
	/*
		NOTE: This function needs to be changed to the new database schema
		Returns scandata over the specified days
	*/
	public function getScanDataByID($id, $days) {
		$time = createDate($days);
		$select = "min(buyoutprice/stacksize) as min,max(buyoutprice/stacksize) as max, avg(buyoutprice/stacksize) as avg,";
		$select .= " stddev_pop(buyoutprice/stacksize) as stddev, sum(stacksize) as count, FROM_UNIXTIME(timestamp, '%c/%d') as day";
		$from = "`scans`";
		$where = "itemID = $id AND FROM_UNIXTIME(timestamp, '%m/%d/%y') > FROM_UNIXTIME($time, '%m/%d/%y')";
		$where .= " AND realm = {$this->serverID} AND buyoutprice > 0";
		//select ($table, $cols = "*", $conditions = "1", $group = "", $limit = "", $debug = false)
		return $this->select($from, $select, $where, "GROUP BY day ASC", false);	
	}
	
	public function getScanSummary($id, $days) {
		$where = "itemid = $id AND date > date_sub(curdate(), INTERVAL $days DAY)";
		return $this->select("scansummary","total,min,max,stddev,avg,moving,date",$where,"",false);	
	}
	
	/*
		NOTE: This function will be replaced by getMovingPrice()
	*/
	public function getPercentilePriceByID($id, $percentile, $days) {
		$where = "itemid = $id AND date > date_sub(curdate(), INTERVAL $days DAY)";
		return $this->selectdata("scansummary", "sum(moving)/count(date) as moving", $where, "", false);
	}
	
	public function getMovingPrice($id, $days) {
		$where = "itemid = $id AND date > date_sub(curdate(), INTERVAL $days DAY)";
		return $this->selectdata("scansummary", "sum(moving)/count(moving)", $where, "", false);
	}
			
	/*
		getSalesDataByDay ( $end, $start = 0, $id = null )
		Retrieves sales data 
		Parameters:
			end		Unix timestamp for the end of the search period
			start	Unix timestamp for the start of the search period
			id		Database's id for an item to limit the search to
		Returns:
			array	sum
					avg
					count	
	*/	
	public function getSalesDataByDay($end, $start = 0, $id = null) {
		if ($start == 0) { $start = time(); }
		$where = "time > $end AND time < $start AND server = {$this->serverID}";
		if ($id) {
			$where .= " AND itemID = $id";
		}
		$result = $this->select("completedAuctions", "sum(money) as sum, count(auctionID) as count",$where);
		$result = $result[0];
		if ($result['sum'] == null || $result['count'] == 0) {
			$result['avg'] = 0;			
			$result['sum'] = 0;
			$result['count'] = 0;
		} else {
			$result['avg'] = floor($result['sum'] / $result['count']);
		}
		return $result;
	}
	
	/*
		getHighestSales
	*/
	public function getHighestSales($end, $count = 1, $start = 0) {
		if ($start == 0) { $start = time(); }
		$sql = 'SELECT items.name as name, items.itemid as itemid, sum(money) as sum, count(completedAuctions.itemID) as count, completedauctions.time as time, FROM_UNIXTIME(completedauctions.time, "%Y %D %M %h:%i:%s %x") as day FROM completedAuctions';
		$sql .=  ' LEFT JOIN items ON completedAuctions.itemID = items.itemID WHERE';
		if ($end != 0) { $sql .= ' time > ' . $end . ' AND time < ' . $start . ' AND'; }
		$sql .= ' server = ' . $this->serverID . ' GROUP BY completedAuctions.itemID ORDER BY sum(money) DESC LIMIT 0,' . $count;
		//echo $sql . "<BR>";
		$result = $this->query($sql);
		if (!$result) {
			$result = array('itemid' => '', 'name' => '', 'sum' => '', 'count' => '');
		} elseif ($count == 1) {
			$result = $result[0];
		}
		return $result;
	}
	
	/*
		NOTE:	This function needs to be updated to use select()
		NOTE:	This function needs to be updated to return a time string for the day
		getIndividiaulHighestSale (	int $start = 0, int count = 1, int $end = 0 )
		Retrieves the item with the highest sales over a given period
		Parameters:
			start	Unix timestamp of beginging of period.
			count	Number of rows to return
			end		Unix timestmap of end of period. Uses current time if not provided
		Returns:
			array	id		Database's id of the item
					name	Name of the item
					money	Ammount of gold earned over time period
					time	Unix timestamp of the day
	*/
	public function getIndividualHighestSale($start = 0, $count = 1, $end = 0) {
		if ($end == 0) { $end = time(); }
		$sql = 'SELECT items.itemid as id, items.name as name, money, time FROM completedAuctions LEFT JOIN items ON completedAuctions.itemID = items.itemID WHERE';
		if ($start != 0) { $sql .= ' time > ' . $start . ' AND time < ' . $end; }
		$sql .=  ' server = ' . $this->serverID . ' ORDER BY money DESC LIMIT 0,' . $count;
		//GROUP BY completedAuctions.itemID 
		$result = $this->query($sql);
		if ($count == 1) {
			$result = $result[0];
		}
		return $result;	
	}
	
	/*
		getHighestSalesDay( int $count = 1 )
		Retrieves the days with the highest gold earned 
		Parameters:
			count	Number of records to return
		Returns:
			array	money	Ammount of gold earned
					day		Day of sales (M/D/YY)
	*/
	public function getHighestSalesDay($count = 1) {
		return $this->select("completedAuctions", "sum(money) as money, FROM_UNIXTIME(time, '%c/%d/%Y') as day", "server = {$this->serverID}", "GROUP BY day ORDER BY money DESC LIMIT 0, $count", false);
		if ($count == 1) { 
			$result = $result[0];
		}
		return $result;
	}	
	
	/*
		This function needs to be altered for the new database schema
		Adds a recipe to the database
	*/
	public function addCraftRecipe($name, $id, $outputNum, $search, $prof) {
		$val = array('spellName' => $name, 'productID' => $id, 'output' => $outputNum, 'search' => $search, 'profID' => $prof);
		$this->insert("crafting", $val, true); 
	}
	
	/*
		getRecipe ( int $id )
		Retrieves a recipe
		Parameters:
			id	Database's id for the recipe
		Returns:
			array	output		Ammount created by recipe
					productID	Database's id for the item produced
					cd			Cooldown of recipe
					profID		Database's id for the profession
	*/
	public function getRecipe($id) {
		return $this->selectrow("crafting", "output, productID, cd, profID", "craftID = $id","",false);
	}
	
	/*
		isCraftable ( int $id, int $allowIgnored)
		Checks if there are any recipes with the given item
		Parameters:
			id				Database's id for the item
			allowIgnored	TO BE DEPRECIATED
		Returns:
			array	craftID
					cd
					output
					profid
	*/	
	public function isCraftable($id, $allowIgnored = 1) {
		$where = "productID = $id";
		if ($allowIgnored == 0) { $where .= " AND search = 1"; }
		return $this->selectrow("crafting", "craftID, cd, output, profid", $where, "", false);
	}
	
	/*	
		This function needs to be altered to serch based on craftID for cases with multiple recipes
		Currently used in itemsales.php, but will be changed.
	*/
	public function getCraftableInputs($id, $allowIgnored = 1) {
		$sql = "SELECT craft.spellName, output.name, input.name, inputID, input.price, inputs.inputCount FROM crafting AS craft";
		$sql .= " LEFT JOIN items AS output ON output.itemID = craft.productID";
		$sql .= " RIGHT JOIN craftinginputs AS inputs ON craft.craftID = inputs.craftID";
		$sql .= " INNER JOIN items as input ON inputs.inputID = input.itemID WHERE craft.productID = $id";
		if ($allowIgnored == 0) { $sql .= " AND craft.search = 1"; }
		return $this->query($sql);
	}
	
	/*
		search( str $str )
		Searches the item database for an item
		Parameters:
			str		A substring or full name of an item to look for
		Returns:
			array	name		Full name of the item
					itemid		Database's id for the item
					icon		Filename of the icon		
	*/
	public function search($str, $limit = 20, $page = 1) {
		$lim = " LIMIT " . ($page - 1) * $limit . " , $limit";		
		$str = mysql_real_escape_string($str);
		return $this->select("items", "name, itemid, icon", "name LIKE '%$str%'", "ORDER BY name ASC" . $lim, false);
	}
	
	/*
		lastUpdate ( )
		Gets the timestamp of the last update to the database
		Parameters:
			None
		Returns:
			int 	Unix timestmap of the last update
	*/
	public function lastUpdate() {
		$result = $this->selectdata("db", "val", "name = 'updated'", "", false);
		if (!$result) {
			$this->insert("db", array('name' => 'updated', 'val' => 1), false);
		}
		return $result;
	}
	
	//********************************************
	// Protected Functions begin here
	//********************************************
	
	protected function createServer($name) {
		$sql = "INSERT INTO realms (name) VALUES ('$name');";
		$this->query($sql);	
	}
	
	/* 
		This function will add a new item to the database with supplied data.
	*/
	protected function createItem($id, $suffix,  $name, $color) {
		$name = $this->formatString($name);
		$color = $this->itemColorToQuality($color);
		$sql = "INSERT INTO items (itemID, suffix, name, quality) VALUES ($id, $suffix, '$name', '$color')";
		$this->query($sql);	
	}
		
	
	/*
		This function will create a new character with a unique id.
	*/	
	protected function createChar($name, $wealth, $server) {
		$sql = "INSERT INTO chars (name, wealth, server) VALUES('$name', $wealth, " . $this->serverID . ")";
		//echo "createChar is creating a new character in the database.<BR>$sql<BR><BR>";
		$this->query($sql);
	}

	/*
		This function will format strings to be used in SQL statements properly.
	*/	
	protected static function formatString($string) {
		return "'" . mysql_real_escape_string($string) . "'";		
	}
	protected static function formatArray(&$arr) {
		foreach ($arr as $key => $val) {
			$arr[$key] = MarketDB::formatString($val);
		}
		return $arr;
	}
	protected static function formatData($mixed) {
		if (!is_array($mixed) && strpos($mixed, ",")) {
			$mixed = split(",", $mixed);
		}
		
		if (is_array($mixed)) {
			return MarketDB::formatArray($mixed);
		} else {
			return MarketDB::formatString($mixed);
		}
	}
		
	protected static function createDate($daysAgo) {
		$day = date("j", time()); $month = date("n", time()); $year = date("Y", time());
		return mktime(0,0,0,$month, $day - $daysAgo, $year);
	}
	
	protected static function itemColorToQuality($str) {
		switch ($str) {
			case "9d9d9d": return 0; break;
			case "ffffff": return 1; break;
			case "1eff00": return 2; break;
			case "0070dd": return 3; break;
			case "a335ee": return 4; break;
			case "ff8000": return 5; break;
		}
	}
	
	protected static function itemQualityToColor($int) {
		switch ($int) {
			case 0: return "9d9d9d"; break;
			case 1: return "ffffff"; break;
			case 2: return "1eff00"; break;
			case 3: return "0070dd"; break;
			case 4: return "a335ee"; break;
			case 5: return "ff8000"; break;
		}
	}
	
	protected function update ($table, $column, $val, $condition = "1", $debug = false) {
		$val = $this->formatData($val);
		$sql = "UPDATE $table SET $column = $val WHERE $condition";
		if ($debug) { echo $sql . "<br />"; }
		$this->query($sql);
	}
	
	//protected function select ($table, $cols = "*", $conditions = "1", $group = "", $limit = "", $debug = false) {
	/*
		Return entire result from a SQL statement with specified data.
			table (string)			table to be used
			cols (mixed)			columns to be returned
			conditions (string)		conditions to be applied (make sure these are indexed on large tables)
			end (string)			GROUP BY, ORDER BY, and LIMIT statements
			debug (boolean)			flag to output SQL statement before querying database
	*/	
	protected function select ($table, $cols = "*", $conditions = "1", $end = "", $debug = false) {
		if ($end) {
			$end = " $end";
		}
		if ($cols != "*" && is_array($cols)) {
			$cols = join(", ", $cols);
		}
		$sql = "SELECT $cols FROM $table WHERE $conditions" . $end . ";";
		if ($debug) {
			print "$sql<BR>\n"; //ob_flush();
			if ($tmp = $this->query($sql)) {
				print "Query was succesful.<BR>";
			} else {
				print "Query was unsuccessful.<BR>";
			} 
			//ob_flush();
			return $tmp;
		} else {
			return $this->query($sql);
		}
	}
	
	// Return all rows and a count of all rows available
	protected function selectcount ($table, $cols = "*", $conditions = "1", $end = "", $debug = false) {
		if ($result = $this->select($table, "SQL_CALC_FOUND_ROWS " . $cols, $conditions, $end, $debug)) {
			$count = current(current($this->query("SELECT FOUND_ROWS();")));
			return array('count' => $count, 'rows' => $result);			
		} else { 
			return false;
		}
	}
	
	// Return only first row from a SQL query on the database
	protected function selectrow ($table, $cols = "*", $conditions = "1", $end = "", $debug = false) {
		if ($result = $this->select($table, $cols, $conditions, $end . " LIMIT 0,1", $debug)) {
			return current($result);
		} else { 
			return false;
		}
	}
	
	// Return only the first value of the first row from a SQL query on the database
	protected function selectdata ($table, $cols = "*", $conditions = "1", $end = "", $debug = false) {
		if ($result = $this->selectrow($table, $cols, $conditions, $end , $debug)) {
			return current($result);
		} else {
			return false;
		}
	}
	
	protected function insert($table, $val, $debug = false) {
		$sql = "INSERT INTO $table (" . join(",",array_keys($val)) . ") VALUES (" . join(",", self::formatData(array_values($val))) . ");";
		if ($debug) {
			print "$sql<BR>";
		} 
		return $this->query($sql);
	
	}
	
	/*
		This function will send a query to the database
	*/
	protected function query($sql) {
		$resultArray = array();
		$result = mysql_query($sql, $this->connection);
		
		if (!$result) {
			echo "Could not successfully run query ($sql) from DB: " . mysql_error();
			exit;	
		} elseif (is_bool($result)) { 
			return $result;
		} else {
			while($row = mysql_fetch_assoc($result)) {
				$resultArray[] = $row;
			}
			mysql_free_result($result);
			return $resultArray;
		}
	}

	protected function error($str) {
		echo '<div style="background-color: #fee; border: 2px dashed red; padding: 5px"><b>Important</b><br /><br />' . $str . '</div>';
	}
}

function arrVal(&$arr, $key) {
	return $arr[$key];
}
?>